package com.ce.cases.source.logic.search;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.ce.cases.source.view.CaseSourceView;
import com.ce.framework.datamaintain.action.SearchAction;
import com.ce.framework.datamaintain.logic.ISQLMapper;
import com.ce.framework.datamaintain.logic.SQLSearchCondition;
import com.ce.framework.exception.AppException;
import com.ce.framework.util.DateUtil;
import com.ce.framework.util.StringUtil;
import com.ce.sys.login.model.User;
import com.ce.sys.org.dao.po.SysVOrg;
import com.ce.util.OrgUtil;

/**
 * 嫦娥软件有限公司 版权所有 2013 创建人：　andy 创建日期：2014-03-18 创建时间：15:29:27 功能描述：备选案源台账
 * [CASE_SOURCE]条件过滤类 ============================================== 修改历史 修改人
 * 修改时间 修改原因
 * 
 * ==============================================
 */
public class CaseSourceSendSearchCondition extends SQLSearchCondition implements
		ISQLMapper {

	private String taxpayerNum;
	private String involvedEnt;
	private String caseNum;
	private String recBeginDate;
	private String recEndDate;
	private String caseFrom;
	private String illegalType;
	private String inspectDept;
	private String collectDept;
	private String opMenu;

	private String curOrgId;
	private String curPersonId;
	private String curOrgLevel;
	private String curOrgType;
	private String curRootOrgId;
	private String curPersonPosition;// 当前人职位
	private String curRootOrgLevel;

	private String state;// 案件状态

	@Override
	public void initCondition(SearchAction action, boolean search)
			throws AppException {
		User user = User.getUser();
		SysVOrg curOrg = OrgUtil.getOrg(User.getUserOrgId());
		this.curOrgId = curOrg.getVcId();
		this.curOrgLevel = curOrg.getVcOrgLevel();
		this.curPersonId = User.getUserId();//
		this.curOrgType = curOrg.getVcType();
		curRootOrgId = user.getRootOrgId();
		curRootOrgLevel = user.getRootOrgLevel();
		curPersonPosition = user.getVcPosition();
	}

	@Override
	public String getExcelName() {
		String excelName = "案源追踪台账";

		return excelName;
	}

	public String getSearchSql() throws AppException
	{
		System.out.println("72.getSearchSql. = 进来了" );
		StringBuffer sql = new StringBuffer(
				"select t.*,hear.ILLEGAL_TYPE_HEAR_NAME,work.DT_DATE downDate,work.VC_STATUS,work.VC_HANDLE_ORGNAME,(select f.VC_STATUS from T_WORKFLOW_FLOW f where f.VC_RECORD_ID=VC_RECORD_ID and f.VC_WORK_ID=work.vc_id and f.VC_SEQUENCE=work.vc_Status) as vcStatus_ from CASE_SOURCE t,case_source_hear hear,T_WORKFLOW_WORK work where t.vc_id=VC_RECORD_ID(+) and t.vc_id=hear.CASE_ID(+) and t.state=3 and t.VC_DEL$FLAG = 0");

		if ("2".equals(curRootOrgLevel))// 跨区局
		{
			sql.append(" and t.INSPECTION_DEPT_ID in (select t.vc_par$orgid from SYS_T_ORG t where t.vc_id='"
					+ curOrgId + "') ");

		} else if ("3".equals(curRootOrgLevel))// 征管局，能看到登记部门是自己部门的。
		{
			sql.append(" and t.COLLECTION_DEPT_ID='" + curRootOrgId
					+ "' and (t.IS_SECRET is null or t.IS_SECRET='0')");
		}

		// 采用变量绑定的方式提高查询效率
		if (StringUtil.isNotBlank(taxpayerNum)) {
			sql.append(" and t.taxpayer_number like ${taxpayerNum}||'%'");
		}
		if (StringUtil.isNotBlank(involvedEnt)) {
			sql.append(" and t.involved_enterprice like '%'||${involvedEnt}||'%'");
		}
		if (StringUtil.isNotBlank(caseNum)) {
			sql.append(" and t.case_number=${caseNum}");
		}
		/*
		 * 2015-06-08 by 刘渝洪，开始 
		 * 修改搜索处理，默认初始列表时，以及搜索日期条件没值时，只搜索出未结案的数据，或者本年度的已结案数据
		 * 以日期条件搜索时，搜索出符合日期条件的全部数据
		 * 原代码：
		 * sql.append(this.getDateTimeCondition("work.Dt_DATE", recBeginDate,recEndDate));
		 * 修改后代码：
		 */
		if (StringUtil.isNotBlank(recBeginDate)	|| StringUtil.isNotBlank(recEndDate))
		{//有搜索时期
			sql.append(this.getDateTimeCondition("work.Dt_DATE", recBeginDate,recEndDate));
			System.out.println("搜索时间=\nsql = " + sql);
		}
		else
		{//搜索时间==NULL
			sql.append(" and (to_number(work.vc_status)<>9 or (to_number(work.vc_status)=9 ");
			String dateBeginOfThisYear = DateUtil.getYear() + "-01-01 00:00:00";
			String dateEndOfThisYear = DateUtil.getYear() + "-12-31 23:59:59";

			sql.append(" and ").append("work.Dt_DATE").append(">=to_date('")
					.append(dateBeginOfThisYear)
					.append("','yyyy-mm-dd hh24:mi:ss') ");
			sql.append(" and ").append("work.Dt_DATE").append("<=to_date('")
					.append(dateEndOfThisYear)
					.append("','yyyy-mm-dd hh24:mi:ss') ");
			sql.append("))");

			System.out.println("没有搜索时间时。默认查询当年1月到12月数据=\nsql = " + sql);
		}
		/* 2015-06-08 by 刘渝洪，结束 */
		if (StringUtil.isNotBlank(caseFrom)) {
			sql.append(" and t.case_from_num=${caseFrom}");
		}
		if (StringUtil.isNotBlank(illegalType)) {
			sql.append(" and t.illegal_type_num=${illegalType}");
		}
		if (StringUtil.isNotBlank(inspectDept)) {
			sql.append(" and t.inspection_dept_id=${inspectDept}");
		}
		if (StringUtil.isNotBlank(collectDept)) {
			sql.append(" and t.collection_dept_id=${collectDept}");
		}

		if (StringUtil.isNotBlank(state)) {
			if ("101".equals(state)) {// 一合并
				sql.append(" and t.VC_JOIN='1'");
			} else if ("102".equals(state)) {// 已撤销
				sql.append(" and t.VC_REVOCATION='2'");
			} else if ("103".equals(state)) {// 已中止(中止检查，中止执行)
				sql.append(" and (t.VC_END_TYPE='1' or t.VC_END_TYPE='3')");
				/*
				 * 2015-05-20 by 刘渝洪，开始
				 * 添加对【流程监控】页面，【案件状态】新增条件项“终止”状态，以及“重启”状态的搜索判断
				 */
			} else if ("104".equals(state)) {// 已终止(终止检查，终止执行)
				sql.append(" and (t.VC_END_TYPE='2' or t.VC_END_TYPE='4')");
				/* 2015-05-20 by 刘渝洪，结束 */
			} else {
				sql.append(" and work.VC_STATUS=${state}");
			}

		}

		// 审批时进行状态过滤
		if ("approve".equals(opMenu)) {

		}
		// 排序
		if (StringUtil.isNotBlank(this.order)) {
			sql.append(" order by " + this.getOrderByUnderLine() + ", t.vc_id");
		} else {
			sql.append(" order by work.VC_HANDLE_DATE desc,t.vc_id");
		}
		return sql.toString();
	}

	public String getCurOrgId() {
		curOrgId = User.getUserOrgId();
		return curOrgId;
	}

	public void setCurOrgId(String curOrgId) {
		this.curOrgId = curOrgId;
	}

	public String getCurPersonId() {
		curPersonId = User.getUserId();
		return curPersonId;
	}

	public void setCurPersonId(String curPersonId) {
		this.curPersonId = curPersonId;
	}

	public Object fromSQLQuery(ResultSet rs, int num) {
		CaseSourceView view = new CaseSourceView();
		try {
			view.setVcId(rs.getString("VC_ID"));
			view.setCaseNumber(rs.getString("CASE_NUMBER"));
			view.setRegisterPerson(rs.getString("REGISTER_PERSON"));
			view.setRegisterDeptId(rs.getString("REGISTER_DEPT_ID"));
			view.setRegisterDept(rs.getString("REGISTER_DEPT"));
			view.setRegisterDate(rs.getDate("REGISTER_DATE"));
			view.setTaxpayerNumber(rs.getString("TAXPAYER_NUMBER"));
			view.setInvolvedEnterprice(rs.getString("INVOLVED_ENTERPRICE"));
			view.setInspectionDeptId(rs.getString("INSPECTION_DEPT_ID"));
			view.setInspectionDept(rs.getString("INSPECTION_DEPT"));
			view.setCollectionDeptId(rs.getString("COLLECTION_DEPT_ID"));
			view.setCollectionDept(rs.getString("COLLECTION_DEPT"));
			view.setIsImportant(rs.getInt("IS_IMPORTANT"));
			view.setIsSecret(rs.getInt("IS_SECRET"));
			view.setCaseFromNum(rs.getString("CASE_FROM_NUM"));
			view.setCaseFromName(rs.getString("CASE_FROM_NAME"));
			view.setIllegalTypeNum(rs.getString("ILLEGAL_TYPE_NUM"));
			view.setIllegalTypeName(rs.getString("ILLEGAL_TYPE_NAME"));
			view.setReceiveDate(rs.getDate("RECEIVE_DATE"));
			view.setState(rs.getInt("VC_STATUS"));
			// view.setStateInfo(rs.getString("STATE_INFO"));
			view.setReportId(rs.getString("REPORT_ID"));
			view.setVcDelFlag(rs.getInt("VC_DEL$FLAG"));
			view.setDownDate(rs.getDate("downDate"));
			view.setVcStatus_(rs.getString("vcStatus_"));
			view.setVcJoin(rs.getString("vc_join"));// 合并标记
			view.setVcRevocation(rs.getString("VC_REVOCATION"));// 撤销标记
			view.setVcEndType(rs.getString("VC_END_TYPE"));
			view.setHearIllegalTypeName(rs.getString("ILLEGAL_TYPE_HEAR_NAME"));
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return view;
	}

	public ISQLMapper getSQLMapper() {

		return this;
	}

	public String getTaxpayerNum() {
		return taxpayerNum;
	}

	public void setTaxpayerNum(String taxpayerNum) {
		this.taxpayerNum = taxpayerNum;
	}

	public String getInvolvedEnt() {
		return involvedEnt;
	}

	public void setInvolvedEnt(String involvedEnt) {
		this.involvedEnt = involvedEnt;
	}

	public String getCaseNum() {
		return caseNum;
	}

	public void setCaseNum(String caseNum) {
		this.caseNum = caseNum;
	}

	public String getRecBeginDate() {
		return recBeginDate;
	}

	public void setRecBeginDate(String recBeginDate) {
		this.recBeginDate = recBeginDate;
	}

	public String getRecEndDate() {
		return recEndDate;
	}

	public void setRecEndDate(String recEndDate) {
		this.recEndDate = recEndDate;
	}

	public String getCaseFrom() {
		return caseFrom;
	}

	public void setCaseFrom(String caseFrom) {
		this.caseFrom = caseFrom;
	}

	public String getIllegalType() {
		return illegalType;
	}

	public void setIllegalType(String illegalType) {
		this.illegalType = illegalType;
	}

	public String getInspectDept() {
		return inspectDept;
	}

	public void setInspectDept(String inspectDept) {
		this.inspectDept = inspectDept;
	}

	public String getCollectDept() {
		return collectDept;
	}

	public void setCollectDept(String collectDept) {
		this.collectDept = collectDept;
	}

	public String getOpMenu() {
		return opMenu;
	}

	public void setOpMenu(String opMenu) {
		this.opMenu = opMenu;
	}

	public String getCurOrgLevel() {
		return curOrgLevel;
	}

	public void setCurOrgLevel(String curOrgLevel) {
		this.curOrgLevel = curOrgLevel;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

}
